﻿-- HIE SYNCHRNOIZATION DATA TABLES
-- TRACKS WHICH DATA HAS BEEN SYNCHRONIZED TO/FROM THE HIE
--
-- ORIGINAL AUTHOR: JUSTIN FYFE
-- DATE: 

-- INDEX: MODIFIED ON
CREATE INDEX "CHILD_MODIFIED_ON" ON "CHILD"("MODIFIED_ON");
CREATE INDEX "CHILD_WEIGHT_MODIFIED_ON" ON "CHILD_WEIGHT"("MODIFIED_ON");
CREATE INDEX "VACCINATION_EVENT_MODIFIED_ON" ON "VACCINATION_EVENT"("MODIFIED_ON");
CREATE INDEX "VACCINATION_APPOINTMENT_MODIFIED_ON" ON "VACCINATION_APPOINTMENT"("MODIFIED_ON");

-- SEQUENCE: HIE SYNC JOB ID SEQUENCE
CREATE SEQUENCE "HIE_SYNC_SEQ" START WITH 1 INCREMENT BY 1;

-- TABLE: SYNC TABLE
-- PURPOSE: TRACKS THE SYNCHRONIZATION JOBS THAT HAVE BEEN STARTED/COMPLETED
CREATE TABLE "HIE_SYNC" (
	"JOB_ID" NUMERIC(20,0) NOT NULL DEFAULT nextval('"HIE_SYNC_SEQ"'), -- UNIQUE IDENTIFIER FOR THE SYNC
	"START_TIMESTAMP" TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP, -- START TIME OF THE JOB
	"STOP_TIMESTAMP" TIMESTAMPTZ, -- STOP / FINISH TIME OF THE JOB
	"OUTCOME" CHAR(1)CHECK ("OUTCOME" IS NULL OR "OUTCOME" IN ('S','E')), -- OUTCOME OF THE JOB EITHER SUCCESS OR ERROR
	"PID" INTEGER NOT NULL, -- THE PROCESS ID OF THE SYNC APP WHEN IT RAN (USED TO LOOKUP IN THE ATNA LOG WHAT WAS DONE)
	CONSTRAINT "PK_HIE_SYNC" PRIMARY KEY ("JOB_ID")
);

-- INDEX: LOOKUP BY SUCCESS
CREATE INDEX "HIE_SYNC_OUTCOME_IDX" ON "HIE_SYNC"("OUTCOME");
-- SEQUENCE: SYNCED PATIENTS SEQUENCE
CREATE SEQUENCE "HIE_PATIENT_SYNC_SEQ" START WITH 1 INCREMENT BY 1;

-- TABLE: SYNCED PATIENTS TABLE
-- PURPOSE: TRACKS THE DATE/TIME THAT A PARTICULAR PATIENT WAS SYNCED WITH THE HIE
CREATE TABLE "HIE_PATIENT_SYNC" (
	"PATIENT_SYNC_ID" NUMERIC(20,0) NOT NULL DEFAULT nextval('"HIE_PATIENT_SYNC_SEQ"'), -- SURROGATE KEY FOR TUPLE
	"JOB_ID" NUMERIC(20,0) NOT NULL, -- THE JOB ID WHEN THIS SYNC RECORD WAS CREATED
	"GIIS_PATIENT_ID" INTEGER NOT NULL, -- THE PATIENT WHICH WAS SYNC
	"SYNC_TIMESTAMP" TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP, -- THE TIME THAT THE SYNC RECORD WAS CREATED
	"ACTION" CHAR(1) NOT NULL CHECK ("ACTION" IN ('C','R','U','D')), -- THE ACTION THAT OCCURRED IN GIIS WHERE READ IS A PUSH TO THE HIE
	"ECID" VARCHAR(20) NOT NULL, -- THE ECID (JURISDICTIONAL IDENTIFIER) FOR THE PATIENT
	CONSTRAINT "PK_HIE_PATIENT_SYNC" PRIMARY KEY ("PATIENT_SYNC_ID"),
	CONSTRAINT "FK_HIE_PATIENT_SYNC_CHILD" FOREIGN KEY ("GIIS_PATIENT_ID") REFERENCES "CHILD"("ID"),
	CONSTRAINT "FK_HIE_PATIENT_SYNC_SYNC_ID" FOREIGN KEY ("JOB_ID") REFERENCES "HIE_SYNC"("JOB_ID")
);

-- GIIS PATIENT IDENTIFIER
CREATE INDEX "HIE_PATIENT_SYNC_CHILD_ID" ON "HIE_PATIENT_SYNC"("GIIS_PATIENT_ID");

-- INDEX: LOOKUP SYNC RECORD BY CHILD'S ID
CREATE INDEX "HIE_PATIENT_SYNC_GIIS_PATIENT_ID" ON "HIE_PATIENT_SYNC"("GIIS_PATIENT_ID");

-- SEQUENCE: DOCUMENT SYNC ID
CREATE SEQUENCE "HIE_DOCUMENT_SYNC_SEQ" START WITH 1 INCREMENT BY 1;

-- TABLE: UPLOADED DOCUMENT / RECORD 
-- PURPOSE: TRACKS WHICH RECORDS WERE SYNCED WITH THE HIE AND AT WHAT TIME
CREATE TABLE "HIE_DOCUMENT_SYNC" (
	"HIE_DOCUMENT_ID"		NUMERIC(20,0) NOT NULL DEFAULT nextval('"HIE_DOCUMENT_SYNC_SEQ"'), -- THE DOCUMENT THIS SYNC RECORD APPLIES TO
	"GIIS_PATIENT_ID"	    INTEGER NOT NULL, -- THE PATIENT TO WHICH THIS DOCUMENT BELONGS
	"SYNC_TIMESTAMP"		TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP, -- THE TIME THAT THE SYNC OCCURRED
	"JOB_ID"				NUMERIC(20,0) NOT NULL, -- THE JOB DURING WHICH THE DATA WAS SYNCED
	"REPLACED_DOC_ID"		NUMERIC(20,0), -- REPLACES DOCUMENT ID
	CONSTRAINT "PK_HIE_DOCUMENT_SYNC" PRIMARY KEY ("HIE_DOCUMENT_ID"),
	CONSTRAINT "FK_HIE_DOCUMENT_SYNC_CHILD" FOREIGN KEY ("GIIS_PATIENT_ID") REFERENCES "CHILD"("ID"),
	CONSTRAINT "FK_HIE_DOCUMENT_SYNC_JOB" FOREIGN KEY ("JOB_ID") REFERENCES "HIE_SYNC"("JOB_ID"),
	CONSTRAINT "FK_HIE_DOCUMENT_SYNC_REPLACE" FOREIGN KEY ("REPLACED_DOC_ID") REFERENCES "HIE_DOCUMENT_SYNC"("HIE_DOCUMENT_ID")
);

-- TABLE: SYNCHRONIZED VACCINATIONS
-- PURPOSE: TRACKS THE DATA IN VACCINATION_EVENT TABLE WITH A HIE_SYNC_DOCUMENT
CREATE TABLE "HIE_DOCUMENT_VACCINATION_EVENT" (
	"HIE_DOCUMENT_ID"		NUMERIC(20,0) NOT NULL, -- THE DOCUMENT TO WHICH THE SYNCHRONIZATION RECORD APPLIES
	"VACCINATION_EVENT_ID"	INTEGER NOT NULL, -- THE VACCINATION EVENT
	"ACTION"				CHAR(1) NOT NULL CHECK ("ACTION" IN ('C','U','D')), -- THE ACTION TAKEN IN THE DOCUMENT (CREATE, UPDATE, DELETE/OBSOLETE)
	CONSTRAINT "PK_HIE_DOCUMENT_VACCINATION_EVENT" PRIMARY KEY ("HIE_DOCUMENT_ID","VACCINATION_EVENT_ID"),
	CONSTRAINT "FK_HIE_DOCUMENT_VACCINATION_EVENT_DOCUMENT" FOREIGN KEY ("HIE_DOCUMENT_ID") REFERENCES "HIE_DOCUMENT_SYNC"("HIE_DOCUMENT_ID"),
	CONSTRAINT "FK_HIE_DOCUMENT_VACCINATION_EVENT_VACCINATION" FOREIGN KEY ("VACCINATION_EVENT_ID") REFERENCES "VACCINATION_EVENT"("ID")
);

-- INDEX: LOOKUP VACCINATIONS REPORTED BY DOCUMENT ID
CREATE INDEX "HIE_DOCUMENT_VACCINATION_EVENT_DOCUMENT_ID" ON "HIE_DOCUMENT_VACCINATION_EVENT"("HIE_DOCUMENT_ID");

-- INDEX: LOOKUP VACCINATIONS REPORTED BY VACCINATION
CREATE INDEX "HIE_DOCUMENT_VACCINATION_EVENT_VACCINATION_ID" ON "HIE_DOCUMENT_VACCINATION_EVENT"("VACCINATION_EVENT_ID");

-- TABLE: SYNCHRONIZED WEIGHTS
-- PURPOSE: TRACKS THE WEIGHTS REPORTED TO THE HIE
CREATE TABLE "HIE_DOCUMENT_CHILD_WEIGHT" (
	"HIE_DOCUMENT_ID"		NUMERIC(20,0) NOT NULL, -- THE DOCUMENT IN WHICH THE WEIGHT WAS REPORTED
	"CHILD_WEIGHT_ID"		INTEGER NOT NULL, -- THE WEIGHT OF THE CHILD RECORD
	"ACTION"				CHAR(1) NOT NULL CHECK("ACTION" IN ('C','U','D')),  -- THE ACTION TAKEN UPON THE ENTRY
	CONSTRAINT "PK_HIE_DOCUMENT_CHILD_WEIGHT" PRIMARY KEY ("HIE_DOCUMENT_ID", "CHILD_WEIGHT_ID"),
	CONSTRAINT "FK_HIE_DOCUMENT_CHILD_WEIGHT_DOCUMENT" FOREIGN KEY ("HIE_DOCUMENT_ID") REFERENCES "HIE_DOCUMENT_SYNC"("HIE_DOCUMENT_ID"),
	CONSTRAINT "FK_HIE_DOCUMENT_CHILD_WEIGHT_WEIGHT" FOREIGN KEY ("CHILD_WEIGHT_ID") REFERENCES "CHILD_WEIGHT"("ID")
);

-- INDEX: LOOKUP WEIGHT REPORTED BY DOCUMENT ID
CREATE INDEX "HIE_DOCUMENT_CHILD_WEIGHT_DOCUMENT_ID" ON "HIE_DOCUMENT_CHILD_WEIGHT"("HIE_DOCUMENT_ID");

-- INDEX: LOOKUP EVENT BY ID
CREATE INDEX "HIE_DOCUMENT_CHILD_WEIGHT_WEIGHT_ID" ON "HIE_DOCUMENT_CHILD_WEIGHT"("CHILD_WEIGHT_ID");

-- TABLE: SYNCHRONIZED WEIGHTS
-- PURPOSE: TRACKS THE WEIGHTS REPORTED TO THE HIE
CREATE TABLE "HIE_DOCUMENT_APPOINTMENT" (
	"HIE_DOCUMENT_ID"		NUMERIC(20,0) NOT NULL, -- THE DOCUMENT IN WHICH THE WEIGHT WAS REPORTED
	"APPOINTMENT_ID"		INTEGER NOT NULL, -- THE WEIGHT OF THE CHILD RECORD
	"ACTION"				CHAR(1) NOT NULL CHECK("ACTION" IN ('C','U','D')),  -- THE ACTION TAKEN UPON THE ENTRY
	CONSTRAINT "PK_HIE_DOCUMENT_APPOINTMENT" PRIMARY KEY ("HIE_DOCUMENT_ID", "APPOINTMENT_ID"),
	CONSTRAINT "FK_HIE_DOCUMENT_APPOINTMENT_DOCUMENT" FOREIGN KEY ("HIE_DOCUMENT_ID") REFERENCES "HIE_DOCUMENT_SYNC"("HIE_DOCUMENT_ID"),
	CONSTRAINT "FK_HIE_DOCUMENT_APPOINTMENT_APPOINTMENT" FOREIGN KEY ("APPOINTMENT_ID") REFERENCES "VACCINATION_APPOINTMENT"("ID")
);

-- INDEX: LOOKUP WEIGHT REPORTED BY DOCUMENT ID
CREATE INDEX "HIE_DOCUMENT_APPOINTMENT_DOCUMENT_ID" ON "HIE_DOCUMENT_APPOINTMENT"("HIE_DOCUMENT_ID");

-- INDEX: LOOKUP EVENT BY ID
CREATE INDEX "HIE_DOCUMENT_APPOINTMENT_APPOINTMENT_ID" ON "HIE_DOCUMENT_APPOINTMENT"("APPOINTMENT_ID");

--
-- FUNCTIONS
-- 

-- FUNCTION: CREATES A NEW HIE SYNCHRONIZATION RECORD
CREATE OR REPLACE FUNCTION HIE_CREATE_SYNC
(
	PID_IN IN INTEGER
) RETURNS NUMERIC AS 
$$
DECLARE
	JOB_ID_VAL NUMERIC(20,0);
BEGIN
	JOB_ID_VAL := nextval('"HIE_SYNC_SEQ"');
	INSERT INTO "HIE_SYNC" ("JOB_ID", "PID") VALUES (JOB_ID_VAL, PID_IN);
	RETURN JOB_ID_VAL;
END;
$$ LANGUAGE plpgsql;

-- FUNCTION: COMPLETES AN HIE SYNCHRONIZATION RECORD
CREATE OR REPLACE FUNCTION HIE_COMPLETE_SYNC
(
	JOB_ID_IN	IN NUMERIC,
	OUTCOME_IN	IN CHAR
) RETURNS VOID AS 
$$
BEGIN
	UPDATE "HIE_SYNC" SET "STOP_TIMESTAMP" = CURRENT_TIMESTAMP, "OUTCOME" = OUTCOME_IN WHERE "JOB_ID" = JOB_ID_IN;
END;
$$ LANGUAGE plpgsql;

-- VIEW: HIE LAST SYNC 
CREATE VIEW "HIE_LAST_SYNC" AS
	SELECT * FROM "HIE_SYNC" WHERE "STOP_TIMESTAMP" IS NOT NULL AND "OUTCOME" = 'S' ORDER BY "STOP_TIMESTAMP" DESC LIMIT 1;


-- FUNCTION: GET THE LAST SYNC JOB THAT WAS COMPLETED
CREATE OR REPLACE FUNCTION HIE_GET_LAST_SYNC
()
RETURNS SETOF "HIE_SYNC" AS 
$$
BEGIN
	RETURN QUERY SELECT * FROM "HIE_LAST_SYNC";
END;
$$ LANGUAGE plpgsql;

-- FUNCTION: REGISTERS A SYNC OPERATION FOR A PARTICULAR PATIENT
CREATE OR REPLACE FUNCTION HIE_CREATE_PATIENT_SYNC
(
	GIIS_PATIENT_ID_IN	IN INTEGER,
	JOB_ID_IN		IN NUMERIC,
	ECID_IN			IN VARCHAR,
	ACTION_IN		IN CHAR
) RETURNS NUMERIC AS 
$$
DECLARE
	PATIENT_SYNC_ID_VAL NUMERIC(20,0);
BEGIN
	PATIENT_SYNC_ID_VAL := nextval('"HIE_PATIENT_SYNC_SEQ"');
	INSERT INTO "HIE_PATIENT_SYNC" ("PATIENT_SYNC_ID","GIIS_PATIENT_ID","ACTION","JOB_ID","ECID") VALUES
		(PATIENT_SYNC_ID_VAL, GIIS_PATIENT_ID_IN, ACTION_IN,JOB_ID_IN, ECID_IN);
	RETURN PATIENT_SYNC_ID_VAL;
END;
$$ LANGUAGE plpgsql;

-- FUNCTION: GET PATIENT SYNC ECID
CREATE OR REPLACE FUNCTION HIE_GET_PATIENT_ECID
(
	GIIS_PATIENT_ID_IN	IN INTEGER
) RETURNS VARCHAR AS
$$
BEGIN
	RETURN (SELECT "ECID" FROM "HIE_PATIENT_SYNC" WHERE "GIIS_PATIENT_ID" = GIIS_PATIENT_ID_IN ORDER BY "SYNC_TIMESTAMP" DESC LIMIT 1);
END
$$ LANGUAGE plpgsql;

-- FUNCTION: GET LAST SYNC DATE
CREATE OR REPLACE FUNCTION HIE_GET_LAST_SYNC_DATE
()
RETURNS TIMESTAMPTZ AS
$$
BEGIN
	RETURN (SELECT "STOP_TIMESTAMP" FROM HIE_GET_LAST_SYNC());
END;
$$ LANGUAGE plpgsql;

-- VIEW: CHILDREN IN GIIS NOT IN THE HIE OR THOSE NEEDING UPDATED INFORMATION
CREATE OR REPLACE VIEW "HIE_CHILD_NOT_IN_HIE" AS 
	SELECT * FROM "CHILD" WHERE "ID" NOT IN (SELECT "GIIS_PATIENT_ID" FROM "HIE_PATIENT_SYNC" WHERE "ID" = "GIIS_PATIENT_ID" AND
		"MODIFIED_ON" <= "SYNC_TIMESTAMP") AND "STATUS_ID" = 1;
			
-- FUNCTION: GET UNSYNCED CHILDREN
CREATE OR REPLACE FUNCTION HIE_GET_UNSYNCED_CHILD
()
RETURNS SETOF "HIE_CHILD_NOT_IN_HIE" AS
$$
BEGIN
	RETURN QUERY SELECT * FROM "HIE_CHILD_NOT_IN_HIE";
END
$$ LANGUAGE plpgsql;

-- VIEW: VACCINATIONS NOT IN THE HIE
CREATE OR REPLACE VIEW "VACCINATION_NOT_IN_HIE" AS 
	SELECT * FROM "VACCINATION_EVENT" WHERE 
		"ID" NOT IN (SELECT "VACCINATION_EVENT_ID" FROM "HIE_DOCUMENT_VACCINATION_EVENT" INNER JOIN "HIE_DOCUMENT_SYNC" USING ("HIE_DOCUMENT_ID") WHERE "HIE_DOCUMENT_VACCINATION_EVENT"."VACCINATION_EVENT_ID" = "VACCINATION_EVENT"."ID" OR
		"MODIFIED_ON" > "SYNC_TIMESTAMP")
		AND "IS_ACTIVE" = TRUE 
		AND ("NONVACCINATION_REASON_ID" <> 0 OR "VACCINATION_STATUS" = TRUE);

-- VIEW: CHILD WEIGHTS NOT IN HIE
CREATE OR REPLACE VIEW "CHILD_WEIGHT_NOT_IN_HIE" AS
	SELECT * FROM "CHILD_WEIGHT" WHERE 
		"ID" NOT IN (SELECT "CHILD_WEIGHT_ID" FROM "HIE_DOCUMENT_CHILD_WEIGHT" INNER JOIN "HIE_DOCUMENT_SYNC" USING ("HIE_DOCUMENT_ID") WHERE "HIE_DOCUMENT_CHILD_WEIGHT"."CHILD_WEIGHT_ID" = "CHILD_WEIGHT"."ID" OR
		"MODIFIED_ON" > "SYNC_TIMESTAMP");

-- VIEW: APPOINTMENTS NOT IN HIE
CREATE OR REPLACE VIEW "APPOINTMENT_NOT_IN_HIE" AS
	SELECT * FROM "VACCINATION_APPOINTMENT" WHERE
		"ID" NOT IN (SELECT "APPOINTMENT_ID" FROM "HIE_DOCUMENT_APPOINTMENT" INNER JOIN "HIE_DOCUMENT_SYNC" USING ("HIE_DOCUMENT_ID")  WHERE "HIE_DOCUMENT_APPOINTMENT"."APPOINTMENT_ID" = "VACCINATION_APPOINTMENT"."ID" OR
		"MODIFIED_ON" > "SYNC_TIMESTAMP") AND
		"AEFI" = TRUE;

-- VIEW: CHILDREN WITH EXPIRED DATA
CREATE VIEW "CHILD_WITH_EXPIRED_HIE_DATA" AS 
	SELECT * FROM "CHILD" WHERE
		"ID" IN (
			SELECT "CHILD_ID" FROM "VACCINATION_NOT_IN_HIE"
			UNION
			SELECT "CHILD_ID" FROM "CHILD_WEIGHT_NOT_IN_HIE"
			UNION
			SELECT "CHILD_ID" FROM "APPOINTMENT_NOT_IN_HIE"
		);

-- FUNCTION: GET CHILD'S ENTERPRISE CLIENT IDENTIFIER
CREATE OR REPLACE FUNCTION HIE_GET_CHILD_ECID
(
	CHILD_ID_IN	IN INTEGER -- IDENTIFIER OF THE CHILD
) RETURNS VARCHAR AS 
$$
BEGIN
	RETURN (SELECT "ECID" FROM "HIE_PATIENT_SYNC" WHERE "GIIS_PATIENT_ID" = CHILD_ID_IN LIMIT 1);
END
$$ LANGUAGE plpgsql;

-- FUNCTION: GET CHILDREN WITH EXPIRED OR UNSYNCED DATA
CREATE OR REPLACE FUNCTION HIE_GET_CHILDREN_WITH_EXPIRED_DATA
() RETURNS SETOF "CHILD_WITH_EXPIRED_HIE_DATA" AS
$$
BEGIN
	RETURN QUERY SELECT * FROM "CHILD_WITH_EXPIRED_HIE_DATA";
END;
$$ LANGUAGE plpgsql;
	
-- FUNCTION: RETURN LAST DOCUMENT IDENTIFIER FOR A CHILD
CREATE OR REPLACE FUNCTION HIE_GET_LAST_DOCUMENT_ID
(	
	CHILD_ID_IN	IN INTEGER
) RETURNS NUMERIC AS 
$$
BEGIN
	RETURN (SELECT "HIE_DOCUMENT_ID" FROM "HIE_DOCUMENT_SYNC" WHERE "GIIS_PATIENT_ID" = CHILD_ID_IN ORDER BY "SYNC_TIMESTAMP" DESC LIMIT 1);
END;
$$ LANGUAGE plpgsql;

-- FUNCTION: GET LAST VACCINATION
CREATE OR REPLACE FUNCTION HIE_GET_UNSYNCED_VACCINATIONS
(
	CHILD_ID_IN IN INTEGER
) RETURNS SETOF "VACCINATION_NOT_IN_HIE" AS 
$$
BEGIN
	RETURN QUERY SELECT * FROM "VACCINATION_NOT_IN_HIE" WHERE "CHILD_ID" = CHILD_ID_IN;
END;
$$ LANGUAGE plpgsql;

-- FUNCTION: GET LAST VACCINATION
CREATE OR REPLACE FUNCTION HIE_GET_UNSYNCED_WEIGHTS
(
	CHILD_ID_IN IN INTEGER
) RETURNS SETOF "CHILD_WEIGHT_NOT_IN_HIE" AS 
$$
BEGIN
	RETURN QUERY SELECT * FROM "CHILD_WEIGHT_NOT_IN_HIE" WHERE "CHILD_ID" = CHILD_ID_IN;
END;
$$ LANGUAGE plpgsql;

-- FUNCTION: GET LAST VACCINATION
CREATE OR REPLACE FUNCTION HIE_GET_UNSYNCED_APPOINTMENTS
(
	CHILD_ID_IN IN INTEGER
) RETURNS SETOF "APPOINTMENT_NOT_IN_HIE" AS 
$$
BEGIN
	RETURN QUERY SELECT * FROM "APPOINTMENT_NOT_IN_HIE" WHERE "CHILD_ID" = CHILD_ID_IN;
END;
$$ LANGUAGE plpgsql;

-- FUNCTION: CREATE A DOCUMENT SYNC RECORD
CREATE OR REPLACE FUNCTION HIE_CREATE_DOCUMENT_SYNC
(
	CHILD_ID_IN IN INTEGER,
	JOB_ID_IN IN NUMERIC,
	REPLACES_DOCUMENT_IN IN NUMERIC
) RETURNS DECIMAL AS
$$
DECLARE
	DOCUMENT_ID_VAL NUMERIC;
BEGIN
	DOCUMENT_ID_VAL := nextval('"HIE_DOCUMENT_SYNC_SEQ"');
	INSERT INTO "HIE_DOCUMENT_SYNC" ("HIE_DOCUMENT_ID", "GIIS_PATIENT_ID", "JOB_ID", "REPLACED_DOC_ID") VALUES 
		(DOCUMENT_ID_VAL, CHILD_ID_IN, JOB_ID_IN, REPLACES_DOCUMENT_IN);
	RETURN DOCUMENT_ID_VAL;
END;
$$ LANGUAGE plpgsql;

-- FUNCTION: REGISTER A VACCINATION SYNCED
CREATE OR REPLACE FUNCTION HIE_CREATE_DOCUMENT_VACCINATION_EVENT
(
	HIE_DOCUMENT_ID_IN IN NUMERIC,
	VACCINATION_EVENT_ID_IN IN INTEGER,
	ACTION_IN IN CHAR
) RETURNS VOID AS
$$
BEGIN
	INSERT INTO "HIE_DOCUMENT_VACCINATION_EVENT" ("HIE_DOCUMENT_ID", "VACCINATION_EVENT_ID", "ACTION") 
		VALUES (HIE_DOCUMENT_ID_IN, VACCINATION_EVENT_ID_IN, ACTION_IN);
END;
$$ LANGUAGE plpgsql;


-- FUNCTION: REGISTER A VACCINATION SYNCED
CREATE OR REPLACE FUNCTION HIE_CREATE_DOCUMENT_CHILD_WEIGHT
(
	HIE_DOCUMENT_ID_IN IN NUMERIC,
	CHILD_WEIGHT_ID_IN IN INTEGER,
	ACTION_IN IN CHAR
) RETURNS VOID AS
$$
BEGIN
	INSERT INTO "HIE_DOCUMENT_CHILD_WEIGHT" ("HIE_DOCUMENT_ID", "CHILD_WEIGHT_ID", "ACTION") 
		VALUES (HIE_DOCUMENT_ID_IN, CHILD_WEIGHT_ID_IN, ACTION_IN);
END;
$$ LANGUAGE plpgsql;


-- FUNCTION: REGISTER A VACCINATION APPOINTMENT SYNCED
CREATE OR REPLACE FUNCTION HIE_CREATE_DOCUMENT_VACCINATION_APPOINTMENT
(
	HIE_DOCUMENT_ID_IN IN NUMERIC,
	VACCINATION_APPOINTMENT_ID_IN IN INTEGER,
	ACTION_IN IN CHAR
) RETURNS VOID AS
$$
BEGIN
	INSERT INTO "HIE_DOCUMENT_APPOINTMENT" ("HIE_DOCUMENT_ID", "APPOINTMENT_ID", "ACTION") 
		VALUES (HIE_DOCUMENT_ID_IN, VACCINATION_APPOINTMENT_ID_IN, ACTION_IN);
END;
$$ LANGUAGE plpgsql;

-- FUNCTION: GET VACCINATION EVENT SYNCED
CREATE OR REPLACE FUNCTION HIE_GET_DOCUMENT_ID_FOR_VACCINATION_EVENT
(
	VACCINATION_EVENT_ID_IN IN INTEGER
) RETURNS NUMERIC AS 
$$
BEGIN
	RETURN (SELECT "HIE_DOCUMENT_ID" FROM "HIE_DOCUMENT_VACCINATION_EVENT" WHERE "VACCINATION_EVENT_ID" = VACCINATION_EVENT_ID_IN ORDER BY "HIE_DOCUMENT_ID" DESC LIMIT 1);
END;
$$ LANGUAGE plpgsql;

-- FUNCTION: GET CHILD WEIGHT SYNCED
CREATE OR REPLACE FUNCTION HIE_GET_DOCUMENT_ID_FOR_CHILD_WEIGHT
(
	CHILD_WEIGHT_ID_IN IN INTEGER
) RETURNS NUMERIC AS 
$$
BEGIN
	RETURN (SELECT "HIE_DOCUMENT_ID" FROM "HIE_DOCUMENT_CHILD_WEIGHT" WHERE "CHILD_WEIGHT_ID" = CHILD_WEIGHT_ID_IN ORDER BY "HIE_DOCUMENT_ID" DESC LIMIT 1);
END;
$$ LANGUAGE plpgsql;

-- FUNCTION: GET VACCINATION APPOINTMENT SYNCED
CREATE OR REPLACE FUNCTION HIE_GET_DOCUMENT_ID_FOR_VACCINATION_APPOINTMENT
(
	VACCINATION_APPOINTMENT_ID_IN IN INTEGER
) RETURNS NUMERIC AS 
$$
BEGIN
	RETURN (SELECT "HIE_DOCUMENT_ID" FROM "HIE_DOCUMENT_APPOINTMENT" WHERE "APPOINTMENT_ID" = VACCINATION_APPOINTMENT_ID_IN ORDER BY "HIE_DOCUMENT_ID" DESC LIMIT 1);
END;
$$ LANGUAGE plpgsql;

